<?php
namespace Operationtest\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\EventManager\EventManagerInterface;
use Zend\View\Model\ViewModel;
use Zend\Session\Container;
use Operationtest\Classe\WhereQueryBySelection;

class PilotageController extends AbstractActionController
{
    protected $saleheadTable;
    protected $salerowTable;
    protected $storeTable;
    protected $marcheTable;
    protected $marcheStoreTable;
    protected $storecountTable;
    protected $rhdataTable;
    protected $operationTable;
    protected $budgetTable;
    
    public function setEventManager(EventManagerInterface $events) {
        parent::setEventManager($events);
 
        $controller = $this;
        $events->attach('dispatch', function ($e) use ($controller) {
            $controller->layout()->setTemplate('layout/operationtest');
        }, 100);
 
        return $this;
    }
    
    public function getSaleheadTable() {
        if (!$this->saleheadTable) {
            $sm = $this->getServiceLocator();
            $this->saleheadTable = $sm->get('Operationtest\Model\SaleheadTable');
        }
        return $this->saleheadTable;
    }
    
    public function getSalerowTable() {
        if (!$this->salerowTable) {
            $sm = $this->getServiceLocator();
            $this->salerowTable = $sm->get('Operationtest\Model\SalerowTable');
        }
        return $this->salerowTable;
    }
    
    public function getStoreTable() {
        if (!$this->storeTable) {
            $sm = $this->getServiceLocator();
            $this->storeTable = $sm->get('Operationtest\Model\StoreTable');
        }
        return $this->storeTable;
    }
    
    public function getMarchestoreTable() {
        if (!$this->marcheStoreTable) {
            $sm = $this->getServiceLocator();
            $this->marcheStoreTable = $sm->get('Operationtest\Model\MarchestoreTable');
        }
        return $this->marcheStoreTable;
    }
    
    public function getMarcheTable() {
        if (!$this->marcheTable) {
            $sm = $this->getServiceLocator();
            $this->marcheTable = $sm->get('Operationtest\Model\MarcheTable');
        }
        return $this->marcheTable;
    }
    
    public function getStorecountTable() {
        if(!$this->storecountTable) {
            $sm = $this->getServiceLocator();
            $this->storecountTable = $sm->get('Operationtest\Model\StorecountTable');
        }
        return $this->storecountTable;
    }
    
    public function getRhdataTable() {
        if (!$this->rhdataTable) {
            $sm = $this->getServiceLocator();
            $this->rhdataTable = $sm->get('Operationtest\Model\RhdataTable');
        }
        return $this->rhdataTable;
    }
    
    public function getOperationTable() {
        if(!$this->operationTable) {
            $sm = $this->getServiceLocator();
            $this->operationTable = $sm->get('RHX\Model\Table\OperationTable');
        }
        return $this->operationTable;
    }
    
    public function getBudgetTable() {
        if(!$this->budgetTable) {
            $sm = $this->getServiceLocator();
            $this->budgetTable = $sm->get('Operationtest\Model\BudgetTable');
        }
        return $this->budgetTable;
    }
    
    public function indexAction() { 
        // List of tables to use
        $tableArray = array(
            'marcheStoreTable' => $this->getMarchestoreTable(), 
            'marcheTable' => $this->getMarcheTable(),
            'storeTable' => $this->getStoreTable()
        );
        
        // Instance of WhereQueryBySelection object
        $whereQueryBySelection = new WhereQueryBySelection($tableArray);
        
        // Get the POST variables
        $request = $this->getRequest();
        
        if ($request->isPost()) {
            $marcheQuerySaleHead = $this->getMarcheQuery($request, 'saleHead.');
            $marcheQueryStoreCount = $this->getMarcheQuery($request, 'StoreCount.');
            $marcheQueryRhData = $this->getMarcheQuery($request, 'RhData.');
            $query = $this->getQuery($request);
            $groupby = " GROUP BY idStore";  
            
            // Get real budgets for each store
            $realGroupby = " GROUP BY idStore, orderDate";
            $rbgWhere = $whereQueryBySelection->realBudgetQueryForPilotage($request, 'orderDate');
            $weekCaBudgetsResult = $this->getSaleheadTable()->getBgCa($rbgWhere, $realGroupby);
            $weekNbTBudgetsResult = $this->getSaleheadTable()->getBgTransaction($rbgWhere, $realGroupby);
            $weekAcBudgetsResult = $this->getSaleheadTable()->getBgAnnulation($rbgWhere, $realGroupby);
            
            $period = array();
            foreach($request->getPost()->periodY AS $year):
                foreach($request->getPost()->periodM AS $month):
                    $month = ($month<10)?'0'.$month:$month;
                    $begin = new \DateTime($year.'-'.$month.'-01');
                    $interval = new \DateInterval('P1D');
                    $end = clone $begin;
                    $end->modify('first day of next month');
                    $jours = new \DatePeriod($begin, $interval, $end);
                    foreach($jours AS $jour):                            
                        $mois = $year.' '.$month;
                        $semaine = $jour->format('W');
                        $dayText = $jour->format('l');
                        $period[$mois][$semaine][$jour->format('Y-m-d')] = $dayText;
                    endforeach;
                endforeach;
            endforeach;
            
            $weekTab = array();
            foreach($period as $idxp => $week):
                $countWeek = 1;
                foreach($week as $idxw => $day):
                    $weekTab[$countWeek][] = $idxw;
                    $countWeek++;
                endforeach;
            endforeach;
            unset($period);
            
            $weekCa = $weekNbT = $weekAc = array();
            foreach($weekTab as $idxw => $week) {
                // CA per week
                foreach($weekCaBudgetsResult as $idx => $wCa) {
                    $orderDate = new \DateTime($wCa['orderDate']);
                    $weekNum = date('W', $orderDate->getTimestamp());
                    
                    if(!isset($weekCa[$wCa['idStore']][$idxw]['ca_ttc']))
                        $weekCa[$wCa['idStore']][$idxw]['ca_ttc'] = 0;
                    if(!isset($weekCa[$wCa['idStore']][$idxw]['ca_ht']))
                        $weekCa[$wCa['idStore']][$idxw]['ca_ht'] = 0;
                
                    if(in_array($weekNum, $week)) {
                        $weekCa[$wCa['idStore']][$idxw]['ca_ttc'] += $wCa['ca_ttc'];
                        $weekCa[$wCa['idStore']][$idxw]['ca_ht'] += $wCa['ca_ht'];
                    }
                }
                
                // Transaction per week
                foreach($weekNbTBudgetsResult as $idx => $wNbT) {
                    $orderDate = new \DateTime($wNbT['orderDate']);
                    $weekNum = date('W', $orderDate->getTimestamp());
                    
                    if(!isset($weekNbT[$wNbT['idStore']][$idxw]['nbT']))
                        $weekNbT[$wNbT['idStore']][$idxw]['nbT'] = 0;
                
                    if(in_array($weekNum, $week)) {
                        $weekNbT[$wNbT['idStore']][$idxw]['nbT'] += $wNbT['nbTransaction'];
                    }
                }
                
                // Annulation caisse per week
                foreach($weekAcBudgetsResult as $idx => $wAc) {
                    $orderDate = new \DateTime($wAc['orderDate']);
                    $weekNum = date('W', $orderDate->getTimestamp());
                    
                    if(!isset($weekAc[$wAc['idStore']][$idxw]['ac']))
                        $weekAc[$wAc['idStore']][$idxw]['ac'] = 0;
                
                    if(in_array($weekNum, $week)) {
                        $weekAc[$wAc['idStore']][$idxw]['ac'] += $wAc['nbAnnulation'];
                    }
                }
            }
            //var_dump($weekAc);
            
            // Get budgets for each store
            $bgWhere = $whereQueryBySelection->budgetQueryForPilotage($request);
            $budgetsResult = $this->getBudgetTable()->getBudgetForPilotage($bgWhere);
            $budgets = $wBudgets = array();
            foreach($budgetsResult as $budget){
                $nbOpenDays = $budget->nbWeek1+$budget->nbWeek2+$budget->nbWeek3+$budget->nbWeek4+$budget->nbWeek5+$budget->nbWeek6;
                
                $budgets[$budget->idStore]['cattc'] = $nbOpenDays * $budget->totttc;
                $budgets[$budget->idStore]['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$nbOpenDays;
                $budgets[$budget->idStore]['nbt'] = ($budget->totttc/$budget->tm)*$nbOpenDays;
                $budgets[$budget->idStore]['fpr'] = $budgets[$budget->idStore]['caht']*$budget->txFpR/100;
                $budgets[$budget->idStore]['pc'] = $budgets[$budget->idStore]['caht']*$budget->txPc/100;
                $budgets[$budget->idStore]['rp'] = $budgets[$budget->idStore]['caht']*$budget->txRp/100;
                $budgets[$budget->idStore]['pc'] = $budgets[$budget->idStore]['caht']*$budget->txPc/100;
                $budgets[$budget->idStore]['a'] = $budgets[$budget->idStore]['caht']*$budget->txA/100;
                $budgets[$budget->idStore]['al'] = $budgets[$budget->idStore]['caht']*$budget->txAl/100;
                $budgets[$budget->idStore]['ta'] = $budgets[$budget->idStore]['a']+$budgets[$budget->idStore]['al'];
                $budgets[$budget->idStore]['e'] = $budgets[$budget->idStore]['caht']*$budget->txE/100;
                
                // Budgets par semaine pour chaque boutique
                $wBudgets[$budget->idStore]['1']['cattc'] = $budget->nbWeek1 * $budget->totttc;
                $wBudgets[$budget->idStore]['2']['cattc'] = $budget->nbWeek2 * $budget->totttc;
                $wBudgets[$budget->idStore]['3']['cattc'] = $budget->nbWeek3 * $budget->totttc;
                $wBudgets[$budget->idStore]['4']['cattc'] = $budget->nbWeek4 * $budget->totttc;
                $wBudgets[$budget->idStore]['5']['cattc'] = $budget->nbWeek5 * $budget->totttc;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['cattc'] = $budget->nbWeek6 * $budget->totttc;
                
                $wBudgets[$budget->idStore]['1']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek1;
                $wBudgets[$budget->idStore]['2']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek2;
                $wBudgets[$budget->idStore]['3']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek3;
                $wBudgets[$budget->idStore]['4']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek4;
                $wBudgets[$budget->idStore]['5']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek5;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek6;
                
                $wBudgets[$budget->idStore]['1']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek1;
                $wBudgets[$budget->idStore]['2']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek2;
                $wBudgets[$budget->idStore]['3']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek3;
                $wBudgets[$budget->idStore]['4']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek4;
                $wBudgets[$budget->idStore]['5']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek5;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek6;
                
                $wBudgets[$budget->idStore]['1']['fpr'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['2']['fpr'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['3']['fpr'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['4']['fpr'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['5']['fpr'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txFpR/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['fpr'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txFpR/100;
                
                $wBudgets[$budget->idStore]['1']['pc'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['2']['pc'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['3']['pc'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['4']['pc'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['5']['pc'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txPc/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['pc'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txPc/100;
                
                $wBudgets[$budget->idStore]['1']['rp'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['2']['rp'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['3']['rp'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['4']['rp'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['5']['rp'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txRp/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['rp'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txRp/100;
                
                $wBudgets[$budget->idStore]['1']['a'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['2']['a'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['3']['a'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['4']['a'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['5']['a'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txA/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['a'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txA/100;
                
                $wBudgets[$budget->idStore]['1']['al'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['2']['al'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['3']['al'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['4']['al'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['5']['al'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txAl/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['al'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txAl/100;
                
                $wBudgets[$budget->idStore]['1']['ta'] = $wBudgets[$budget->idStore]['1']['a']+$wBudgets[$budget->idStore]['1']['al'];
                $wBudgets[$budget->idStore]['2']['ta'] = $wBudgets[$budget->idStore]['2']['a']+$wBudgets[$budget->idStore]['2']['al'];
                $wBudgets[$budget->idStore]['3']['ta'] = $wBudgets[$budget->idStore]['3']['a']+$wBudgets[$budget->idStore]['3']['al'];
                $wBudgets[$budget->idStore]['4']['ta'] = $wBudgets[$budget->idStore]['4']['a']+$wBudgets[$budget->idStore]['4']['al'];
                $wBudgets[$budget->idStore]['5']['ta'] = $wBudgets[$budget->idStore]['5']['a']+$wBudgets[$budget->idStore]['5']['al'];
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['ta'] = $wBudgets[$budget->idStore]['6']['a']+$wBudgets[$budget->idStore]['6']['al'];
                
                $wBudgets[$budget->idStore]['1']['e'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['2']['e'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['3']['e'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['4']['e'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['5']['e'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txE/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['e'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txE/100;
            }
            //var_dump($budgets);
            
            // Get budgets for each market
            $marcheBudgets = array();
            $marcheBgWhere = $whereQueryBySelection->budgetMarcheQueryForPilotage($request);
            if($marcheBgWhere['withMarche'] == 1) {
                $marcheBudgetsResult = $this->getBudgetTable()->getMarcheBudgetForPilotage($marcheBgWhere);            
                foreach($marcheBudgetsResult as $marcheBudget){
                    $nbOpenDays = $marcheBudget->nbWeek1+$marcheBudget->nbWeek2+$marcheBudget->nbWeek3+$marcheBudget->nbWeek4+$marcheBudget->nbWeek5+$marcheBudget->nbWeek6;
                    
                    @$marcheBudgets[$marcheBudget->idMarche]['cattc'] += $nbOpenDays * $marcheBudget->totttc;
                    @$marcheBudgets[$marcheBudget->idMarche]['caht'] += (($marcheBudget->totttc*0.995/1.07)+($marcheBudget->totttc*0.005/1.196))*$nbOpenDays;
                    @$marcheBudgets[$marcheBudget->idMarche]['nbt'] += ($marcheBudget->totttc/$marcheBudget->tm)*$nbOpenDays;
                    @$marcheBudgets[$marcheBudget->idMarche]['fpr'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txFpR/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['pc'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txPc/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['rp'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txRp/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['a'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txA/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['al'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txAl/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['ta'] = $marcheBudgets[$marcheBudget->idMarche]['a']+$$marcheBudgets[$marcheBudget->idMarche]['al'];
                    @$marcheBudgets[$marcheBudget->idMarche]['e'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txE/100;
                }
            }
            //var_dump($marcheBudgets);
            
            // MISE EN SESSION DES CRITERES DANS SELECTION DU PERIMETRE
            $dataCritere = new Container('dataCritere');
            $dataCritere->getManager()->getStorage()->clear('dataCritere');
            
            if($request->getPost()->periodY)
                $dataCritere->periodY = $request->getPost()->periodY;
            if($request->getPost()->periodM)
                $dataCritere->periodM = $request->getPost()->periodM;
            if($request->getPost()->semaine)
                $dataCritere->semaine = $request->getPost()->semaine;
            if($request->getPost()->specific_date)
                $dataCritere->specific_date = $request->getPost()->specific_date;
            
            // POUR LES PAVES SITES ET MARCHES            
            $classStore = array();
            $marcheStore = $this->getMarchestoreTable()->getStoreMarche();
            foreach($marcheStore as $storeitem) {
                $classStore[$storeitem['idStore']][] = $storeitem['idMarche'];
                
            }
            
            $storeIds = array();
            $marcheIds = array();            
            $storeName = "";
            foreach($request->getPost()->selectable as $storeId){
                $tabStoreId = explode("-", $storeId);
                if(count($tabStoreId) == 1) {
                    // Pour les idStores
                    $storeIds[] = $storeId;
                    
                    // Pour les noms
                    $store = $this->getStoreTable()->getStore($storeId);
                    if($storeName == "")
                        $storeName .= $store->name;
                    else
                        $storeName .= " - ".$store->name;
                } else {
                    // Pour les idStores
                    $marcheIds[] = $tabStoreId[1];
                    $mStores = $this->getMarchestoreTable()->getMarcheStore($tabStoreId[1]);
                    if($tabStoreId[1] == 1) { // Si le marché Cojean est choisi
                        $stores = $this->getStoreTable()->fetchAll();
                        foreach ($stores as $store) {
                            $storeIds[] = $store->idStore;                            
                        }
                    } else { // Si autre marché que Cojean est choisi
                        foreach($mStores as $mstore) {
                            $storeIds[] = $mstore['idStore'];
                        }
                    }
                    
                    // Pour les noms
                    $marche = $this->getMarcheTable()->getMarche($tabStoreId[1]);
                    if($storeName == "")
                        $storeName .= $marche->libelle;
                    else
                        $storeName .= " - ".$marche->libelle;
                }
            }
            
            $caCumulMarche = $tmCumulMarche = $nbTCumulMarche = $acCumulMarche = $nbACumulMarche = $ecCumulMarche = array();
            if($marcheQuerySaleHead['withMarche'] == 1) {
                $caCumulMarche = $this->getSaleheadTable()->getBgMarcheCa($marcheQuerySaleHead['where']);
                $tmCumulMarche = $this->getSaleheadTable()->getBgMarcheTmTtc($marcheQuerySaleHead['where']);
                $nbTCumulMarche = $this->getSaleheadTable()->getBgMarcheTransaction($marcheQuerySaleHead['where']);
                $acCumulMarche = $this->getSaleheadTable()->getBgMarcheAnnulation($marcheQuerySaleHead['where']);
                $nbACumulMarche = $this->getSalerowTable()->getNbMarcheArticle($marcheQuerySaleHead['where']);
                $ecCumulMarche = $this->getStorecountTable()->getBgMarcheEcarts($marcheQueryStoreCount['whereOther']);
                $moCumulMarche = $this->getRhdataTable()->getBgMarcheMO($marcheQueryRhData['whereOther']);
            }
            
            $marches = array();
            foreach($this->getMarcheTable()->listAll() as $marche) {
                $marches[$marche->idMarche] = $marche->libelle;
            }
            
            return new ViewModel(array(
                'monthText' => $this->echoMonthText(),

                ///////// PERIODE TEXT
                'periodText' => $query['period'],
                
                ///////// STORE
                'storeIds' => $storeIds,
                'classStore' => $classStore,
                'storeName' => $storeName,
                'stores' => $this->getStoreTable()->fetchAll(),
                'stores2' => $this->getStoreTable()->fetchAll(),
                
                ///////// MARCHE
                'marcheIds' => $marcheIds,
                'marches' => $marches,
                
                'ca' => $this->getSaleheadTable()->getBgCa($query['where'], $groupby),
                'weekCa' => $weekCa,
                'tm' => $this->getSaleheadTable()->getBgTmTtc($query['where'], $groupby),
                'nbT' => $this->getSaleheadTable()->getBgTransaction($query['where'], $groupby),
                'weekNbT' => $weekNbT,
                'nbA' => $this->getSalerowTable()->getNbArticle($query['where'], $groupby),
                'ec' => $this->getStorecountTable()->getBgEcarts($query['whereOther'], $groupby),
                'ac' => $this->getSaleheadTable()->getBgAnnulation($query['where'], $groupby),
                'weekAc' => $weekAc,
                'mo' => $this->getRhdataTable()->getBgMO($query['whereOther'], $groupby),
                'budgets' => $budgets,
                'wBudgets' => $wBudgets,
                
                'caCumulMarche' => $caCumulMarche,
                'tmCumulMarche' => $tmCumulMarche,
                'nbTCumulMarche' => $nbTCumulMarche,
                'acCumulMarche' => $acCumulMarche,
                'nbACumulMarche' => $nbACumulMarche,
                'ecCumulMarche' => $ecCumulMarche,
                'moCumulMarche' => @$moCumulMarche,
                'marcheBudgets' => $marcheBudgets,
            )); 
        } else {
            return $this->redirect()->toRoute('operationtest/default');
        }        
    } 
    
    public function refreshAction() {
        $request = $this->getRequest();
        if ($request->isPost()) {             
            $marcheQuery = $this->getRefreshMarcheQuery($request->getPost()->marcheids, 'MarcheStore.');
            $query = $this->getRefreshQuery($request->getPost()->sotreids);
            $groupby = " GROUP BY idStore";
            
            // Utilisation de WhereQueryBySelection pour les requetes
            $whereQueryBySelection = new WhereQueryBySelection();
            
            // Get budget for stores selected
            $bgWhere = $whereQueryBySelection->budgetRefreshQueryForPilotage($request->getPost()->sotreids);
            $budgetsResult = $this->getBudgetTable()->getBudgetForPilotage($bgWhere);
            $budgets = $wBudgets = array();
            foreach($budgetsResult as $budget){
                $nbOpenDays = $budget->nbWeek1+$budget->nbWeek2+$budget->nbWeek3+$budget->nbWeek4+$budget->nbWeek5+$budget->nbWeek6;
                
                $budgets[$budget->idStore]['cattc'] = $nbOpenDays * $budget->totttc;
                $budgets[$budget->idStore]['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$nbOpenDays;
                $budgets[$budget->idStore]['nbt'] = ($budget->totttc/$budget->tm)*$nbOpenDays;
                $budgets[$budget->idStore]['nbt'] = ($budget->totttc/$budget->tm)*$nbOpenDays;
                $budgets[$budget->idStore]['fpr'] = $budgets[$budget->idStore]['caht']*$budget->txFpR/100;
                $budgets[$budget->idStore]['pc'] = $budgets[$budget->idStore]['caht']*$budget->txPc/100;
                $budgets[$budget->idStore]['rp'] = $budgets[$budget->idStore]['caht']*$budget->txRp/100;
                $budgets[$budget->idStore]['pc'] = $budgets[$budget->idStore]['caht']*$budget->txPc/100;
                $budgets[$budget->idStore]['a'] = $budgets[$budget->idStore]['caht']*$budget->txA/100;
                $budgets[$budget->idStore]['al'] = $budgets[$budget->idStore]['caht']*$budget->txAl/100;
                $budgets[$budget->idStore]['ta'] = $budgets[$budget->idStore]['a']+$budgets[$budget->idStore]['al'];
                $budgets[$budget->idStore]['e'] = $budgets[$budget->idStore]['caht']*$budget->txE/100;
                
                // Pour les semaines
                $wBudgets[$budget->idStore]['1']['cattc'] = $budget->nbWeek1 * $budget->totttc;
                $wBudgets[$budget->idStore]['2']['cattc'] = $budget->nbWeek2 * $budget->totttc;
                $wBudgets[$budget->idStore]['3']['cattc'] = $budget->nbWeek3 * $budget->totttc;
                $wBudgets[$budget->idStore]['4']['cattc'] = $budget->nbWeek4 * $budget->totttc;
                $wBudgets[$budget->idStore]['5']['cattc'] = $budget->nbWeek5 * $budget->totttc;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['cattc'] = $budget->nbWeek6 * $budget->totttc;
                
                $wBudgets[$budget->idStore]['1']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek1;
                $wBudgets[$budget->idStore]['2']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek2;
                $wBudgets[$budget->idStore]['3']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek3;
                $wBudgets[$budget->idStore]['4']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek4;
                $wBudgets[$budget->idStore]['5']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek5;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['caht'] = (($budget->totttc*0.995/1.07)+($budget->totttc*0.005/1.196))*$budget->nbWeek6;
                
                $wBudgets[$budget->idStore]['1']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek1;
                $wBudgets[$budget->idStore]['2']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek2;
                $wBudgets[$budget->idStore]['3']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek3;
                $wBudgets[$budget->idStore]['4']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek4;
                $wBudgets[$budget->idStore]['5']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek5;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['nbt'] = ($budget->totttc/$budget->tm)*$budget->nbWeek6;
                
                $wBudgets[$budget->idStore]['1']['fpr'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['2']['fpr'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['3']['fpr'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['4']['fpr'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txFpR/100;
                $wBudgets[$budget->idStore]['5']['fpr'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txFpR/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['fpr'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txFpR/100;
                
                $wBudgets[$budget->idStore]['1']['pc'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['2']['pc'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['3']['pc'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['4']['pc'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txPc/100;
                $wBudgets[$budget->idStore]['5']['pc'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txPc/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['pc'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txPc/100;
                
                $wBudgets[$budget->idStore]['1']['rp'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['2']['rp'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['3']['rp'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['4']['rp'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txRp/100;
                $wBudgets[$budget->idStore]['5']['rp'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txRp/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['rp'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txRp/100;
                
                $wBudgets[$budget->idStore]['1']['a'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['2']['a'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['3']['a'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['4']['a'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txA/100;
                $wBudgets[$budget->idStore]['5']['a'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txA/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['a'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txA/100;
                
                $wBudgets[$budget->idStore]['1']['al'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['2']['al'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['3']['al'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['4']['al'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txAl/100;
                $wBudgets[$budget->idStore]['5']['al'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txAl/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['al'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txAl/100;
                
                $wBudgets[$budget->idStore]['1']['ta'] = $wBudgets[$budget->idStore]['1']['a']+$wBudgets[$budget->idStore]['1']['al'];
                $wBudgets[$budget->idStore]['2']['ta'] = $wBudgets[$budget->idStore]['2']['a']+$wBudgets[$budget->idStore]['2']['al'];
                $wBudgets[$budget->idStore]['3']['ta'] = $wBudgets[$budget->idStore]['3']['a']+$wBudgets[$budget->idStore]['3']['al'];
                $wBudgets[$budget->idStore]['4']['ta'] = $wBudgets[$budget->idStore]['4']['a']+$wBudgets[$budget->idStore]['4']['al'];
                $wBudgets[$budget->idStore]['5']['ta'] = $wBudgets[$budget->idStore]['5']['a']+$wBudgets[$budget->idStore]['5']['al'];
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['ta'] = $wBudgets[$budget->idStore]['6']['a']+$wBudgets[$budget->idStore]['6']['al'];
                
                $wBudgets[$budget->idStore]['1']['e'] = $wBudgets[$budget->idStore]['1']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['2']['e'] = $wBudgets[$budget->idStore]['2']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['3']['e'] = $wBudgets[$budget->idStore]['3']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['4']['e'] = $wBudgets[$budget->idStore]['4']['caht']*$budget->txE/100;
                $wBudgets[$budget->idStore]['5']['e'] = $wBudgets[$budget->idStore]['5']['caht']*$budget->txE/100;
                if(isset($weekTab[$budget->idStore]['6']))
                    $wBudgets[$budget->idStore]['6']['e'] = $wBudgets[$budget->idStore]['6']['caht']*$budget->txE/100;
            }
            //var_dump($budgets);
            
            // Get budget for market selected
            $bgMarcheWhere = $whereQueryBySelection->budgetMarcheRefreshQueryForPilotage($request->getPost()->marcheids);
            $marcheBudgets = array();
            
            if($bgMarcheWhere['withMarche'] == 1) {
                $budgetMarcheResult = $this->getBudgetTable()->getMarcheBudgetForPilotage($bgMarcheWhere);

                foreach($budgetMarcheResult as $marcheBudget) {
                    $nbOpenDays = $marcheBudget->nbWeek1+$marcheBudget->nbWeek2+$marcheBudget->nbWeek3+$marcheBudget->nbWeek4+$marcheBudget->nbWeek5+$marcheBudget->nbWeek6;
                    
                    @$marcheBudgets[$marcheBudget->idMarche]['cattc'] += $nbOpenDays * $marcheBudget->totttc;
                    @$marcheBudgets[$marcheBudget->idMarche]['caht'] += (($marcheBudget->totttc*0.995/1.07)+($marcheBudget->totttc*0.005/1.196))*$nbOpenDays;
                    @$marcheBudgets[$marcheBudget->idMarche]['nbt'] += ($marcheBudget->totttc/$marcheBudget->tm)*$nbOpenDays;
                    @$marcheBudgets[$marcheBudget->idMarche]['fpr'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txFpR/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['pc'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txPc/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['rp'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txRp/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['a'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txA/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['al'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txAl/100;
                    @$marcheBudgets[$marcheBudget->idMarche]['ta'] = $marcheBudgets[$marcheBudget->idMarche]['a']+$$marcheBudgets[$marcheBudget->idMarche]['al'];
                    @$marcheBudgets[$marcheBudget->idMarche]['e'] = $marcheBudgets[$marcheBudget->idMarche]['caht']*$marcheBudget->txE/100;
                }
            }
            //var_dump($marcheBudgets);
            
            $storeIds = array();
            if($request->getPost()->sotreids != "")
                $storeIds = explode(',', $request->getPost()->sotreids);
            
            $marcheIds = array();
            if($request->getPost()->marcheids != "")
                $marcheIds = explode(',', $request->getPost()->marcheids);
            
            // Pour les noms des boutiques 
            $storeName = "";
            foreach($storeIds as $storeId) {
                $store = $this->getStoreTable()->getStore($storeId);
                if($storeName == "")
                    $storeName .= $store->name;
                else
                    $storeName .= " - ".$store->name;
            }
            
            $ca = $tm = $nbT = $ac = $nbA = $ec = $mo = array();
            if(!empty($query)) {
                $ca = $this->getSaleheadTable()->getBgCa($query['where'], $groupby);
                $tm = $this->getSaleheadTable()->getBgTmTtc($query['where'], $groupby);
                $nbT = $this->getSaleheadTable()->getBgTransaction($query['where'], $groupby);                
                $nbA = $this->getSalerowTable()->getNbArticle($query['where'], $groupby);
                $ac = $this->getSaleheadTable()->getBgAnnulation($query['where'], $groupby);
                $ec = $this->getStorecountTable()->getBgEcarts($query['whereOther'], $groupby);
                $mo = $this->getRhdataTable()->getBgMO($query['whereOther'], $groupby);
            }
            
            $caCumulMarche = $tmCumulMarche = $nbTCumulMarche = $acCumulMarche = $nbACumulMarche = $ecCumulMarche = $moCumulMarche = array();
            if(!empty($marcheQuery)) {
                $caCumulMarche = $this->getSaleheadTable()->getBgMarcheCa($marcheQuery['where']);
                $tmCumulMarche = $this->getSaleheadTable()->getBgMarcheTmTtc($marcheQuery['where']);
                $nbTCumulMarche = $this->getSaleheadTable()->getBgMarcheTransaction($marcheQuery['where']);
                $acCumulMarche = $this->getSaleheadTable()->getBgMarcheAnnulation($marcheQuery['where']);
                $nbACumulMarche = $this->getSalerowTable()->getNbMarcheArticle($marcheQuery['where']);
                $ecCumulMarche = $this->getStorecountTable()->getBgMarcheEcarts($marcheQuery['whereOther']);
                $moCumulMarche = $this->getRhdataTable()->getBgMarcheMO($marcheQuery['whereOther']);
            }
            
            $marches = array();
            foreach($this->getMarcheTable()->listAll() as $marche) {
                $marches[$marche->idMarche] = $marche->libelle;
            }
                    
            $variables = array(
                'ca' => $ca,
                'tm' => $tm,
                'nbT' => $nbT,
                'nbA' => $nbA,
                'ac' => $ac,
                'ec' => $ec,
                'mo' => $mo,
                'budgets' => $budgets,
                'wBudgets' => $wBudgets,
                'marcheBudgets' => $marcheBudgets,
                
                'caCumulMarche' => $caCumulMarche,
                'tmCumulMarche' => $tmCumulMarche,
                'nbTCumulMarche' => $nbTCumulMarche,
                'acCumulMarche' => $acCumulMarche,
                'nbACumulMarche' => $nbACumulMarche,
                'ecCumulMarche' => $ecCumulMarche,
                'moCumulMarche' => $moCumulMarche,
                
                'stores' => $this->getStoreTable()->fetchAll(),
                'storeIds' => $storeIds,
                
                'marcheIds' => $marcheIds,
                'marches' => $marches,
            );
            
            $viewModel = new ViewModel();
            $viewModel->setVariables($variables);
            $viewModel->setTerminal(true);
            
            return $viewModel; 
        } else {
            return $this->redirect()->toRoute('operationtest/default');
        }        
    }
    
    public function refreshstorenameAction() {
        $request = $this->getRequest();
        if ($request->isPost()) {
            $storeIds = explode(',', $request->getPost()->sotreids);
            $marcheIds = explode(',', $request->getPost()->marcheids);
            
            // Pour les noms des boutiques 
            $storeName = "";
            
            if($request->getPost()->marcheids != "") {
                foreach($marcheIds as $marcheId) {
                    $marche = $this->getMarcheTable()->getMarche($marcheId);
                    if($storeName == "")
                        $storeName .= $marche->libelle;
                    else 
                        $storeName .= " - ".$marche->libelle;
                }
            }
            
            if($request->getPost()->sotreids != "") {
                foreach($storeIds as $storeId) {
                    $store = $this->getStoreTable()->getStore($storeId);
                    if($storeName == "")
                        $storeName .= $store->name;
                    else
                        $storeName .= " - ".$store->name;
                }
            }
                    
            $variables = array(
                'storeName' => ucwords(mb_strtolower($storeName, 'UTF-8')),
            );
            
            $viewModel = new ViewModel();
            $viewModel->setVariables($variables);
            $viewModel->setTerminal(true);
            
            return $viewModel; 
        } else {
            return $this->redirect()->toRoute('operationtest/default');
        }        
    }
    
    function getQuery($request) {
        $storeIds = array();
        foreach($request->getPost()->selectable as $storeId){
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) == 1) { // Récupères les boutiques choisies
                $storeIds[] = $storeId;
            } else { // Récupère les boutiques du marché choisi
                $mStores = $this->getMarchestoreTable()->getMarcheStore($tabStoreId[1]);
                if($tabStoreId[1] == 1) { // Si le marché Cojean est choisi
                    $stores = $this->getStoreTable()->fetchAll();
                    foreach ($stores as $store) {
                        $storeIds[] = $store->idStore;
                    }
                } else { // Si autre marché que Cojean est choisi
                    foreach($mStores as $mstore) {
                        $storeIds[] = $mstore['idStore'];
                    }
                }
            }
        }
        $storeWhere = "idStore IN (".implode(',', $storeIds).")";
        $whereS = $storeWhere;
        $whereOther = $storeWhere;
        $whereS2 = "";
        $whereOther2 = "";
        $query = array();

        if($request->getPost()->periodY){
            $counterA = 0;
            if($request->getPost()->periodM){
                if($request->getPost()->semaine){ ////////////// Année + Mois + Semaine
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            foreach($request->getPost()->semaine as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                                
                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;
                        endforeach;
                        $counterA++;
                    endforeach;
                } else { ////////////// Année + Mois
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            } else {
                                $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }
                        endforeach;

                        $counterA++;
                    endforeach;
                }

                if($counterA == count($request->getPost()->periodY)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } else {
                if($request->getPost()->semaine){ ////////////// Année + Semaine 
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->semaine as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                        endforeach;

                        $counterA++;
                    endforeach;
                } else { ////////////// Année
                    foreach($request->getPost()->periodY as $year):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                        } else {
                            $whereS2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereOther2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }
                        $counterA++;
                    endforeach;
                }
                if($counterA == count($request->getPost()->periodY)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } 
        } else {
            if($request->getPost()->periodM){
                $counterM = 0;
                if($request->getPost()->semaine){ ////////////// Mois + Semaine
                    foreach($request->getPost()->periodM as $month):
                        foreach($request->getPost()->semaine as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                            
                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month;
                                $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                            }
                        endforeach;

                        $counterM++;
                    endforeach;
                } else { ////////////// Mois
                    foreach($request->getPost()->periodM as $month):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                        } else {
                            $whereS2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereOther2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }

                        $counterM++;
                    endforeach;
                }
                if($counterM == count($request->getPost()->periodM)){
                    $whereS2 .= " )";
                    $whereOther2 .= " )";
                }
            } else {
                if($request->getPost()->semaine){  ////////////// Semaine
                    $counterS = 0;
                    foreach($request->getPost()->semaine as $day):
                        if($whereS2 == "") {
                            $whereS2 .= " AND ( DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        } else {
                            $whereS2 .= " OR DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        }
                        
                        if($whereOther2 == "") {
                            $whereOther2 .= " AND ( DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        } else {
                            $whereOther2 .= " OR DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                        }

                        $counterS++;
                    endforeach;

                    if($counterS == count($request->getPost()->semaine)) {
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                }
            }
        }

        
        $whereS .= $whereS2;        
        $whereOther .= $whereOther2;  
        

        ///////////////////// PERIODE TEXT ///////////////////////
        $period = "";
        $periodM = "";
        $periodY = "";
        $periodJ = "";
        $monthText = $this->echoMonthText();
        $dayText = $this->echoShortDayText();

        if($request->getPost()->periodM){
            $i = 1;
            foreach($request->getPost()->periodM as $mois):
                if($i < count($request->getPost()->periodM))
                    $periodM .= $monthText[$mois].", ";
                else
                    $periodM .= $monthText[$mois];
                $i++;
            endforeach;
        }
        if($request->getPost()->periodY){
            $i = 1;
            $periodY .= ' ';
            foreach($request->getPost()->periodY as $annee):                    
                if($i < count($request->getPost()->periodY))
                    $periodY .= $annee.", ";
                else
                    $periodY .= $annee;
                $i++;
            endforeach;
        }
        if($request->getPost()->semaine){
            $i = 1;
            $periodJ .= ' - ';
            foreach($request->getPost()->semaine as $jour):                    
                if($i < count($request->getPost()->semaine))
                    $periodJ .= $dayText[$jour]." ";
                else
                    $periodJ .= $dayText[$jour];
                $i++;
            endforeach;
        }

        $period = $periodM.$periodY.$periodJ;
        $period = ($period != "")?$period:"Toute la période";

        $query['where'] = $whereS;
        $query['whereOther'] = $whereOther;
        $query['period'] = $period;

        return $query;        
    }
    
    function getMarcheQuery($request, $prefix="") {
        $storeIds = array();
        $withMarche = 0;
        $inMarche = "";
        $inMarcheQuery = "";
        foreach($request->getPost()->selectable as $storeId){
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) > 1) {
                $withMarche = 1;
                if($inMarche == "") {
                    $inMarche = $tabStoreId[1];
                } else {
                    $inMarche .= ",".$tabStoreId[1];
                }
                
                $mStores = $this->getMarchestoreTable()->getMarcheStore($tabStoreId[1]);
                foreach($mStores as $mstore) {
                    $storeIds[] = $mstore['idStore'];
                }
            }
        }
        
        if($inMarche != "")
            $inMarcheQuery = " AND MarcheStore.idMarche IN (".$inMarche.")";            
        
        $query = array();
        if($withMarche == 1) {
            $storeWhere = $prefix."idStore IN (".implode(',', $storeIds).")".$inMarcheQuery;
            $whereS = $storeWhere;
            $whereOther = $storeWhere;
            $whereS2 = "";
            $whereOther2 = "";            

            if($request->getPost()->periodY){
                $counterA = 0;
                if($request->getPost()->periodM){
                    if($request->getPost()->semaine){ ////////////// Année + Mois + Semaine
                        foreach($request->getPost()->periodY as $year):
                            foreach($request->getPost()->periodM as $month):
                                foreach($request->getPost()->semaine as $day):
                                    if($whereS2 == "") {
                                        $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }

                                    if($whereOther2 == "") {
                                        $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }
                                endforeach;
                            endforeach;
                            $counterA++;
                        endforeach;
                    } else { ////////////// Année + Mois
                        foreach($request->getPost()->periodY as $year):
                            foreach($request->getPost()->periodM as $month):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    }

                    if($counterA == count($request->getPost()->periodY)){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($request->getPost()->semaine){ ////////////// Année + Semaine 
                        foreach($request->getPost()->periodY as $year):
                            foreach($request->getPost()->semaine as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    } else { ////////////// Année
                        foreach($request->getPost()->periodY as $year):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                            } else {
                                $whereS2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                            } else {
                                $whereOther2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                            }
                            $counterA++;
                        endforeach;
                    }
                    if($counterA == count($request->getPost()->periodY)){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } 
            } else {
                if($request->getPost()->periodM){
                    $counterM = 0;
                    if($request->getPost()->semaine){ ////////////// Mois + Semaine
                        foreach($request->getPost()->periodM as $month):
                            foreach($request->getPost()->semaine as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterM++;
                        endforeach;
                    } else { ////////////// Mois
                        foreach($request->getPost()->periodM as $month):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                            } else {
                                $whereS2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                            } else {
                                $whereOther2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                            }

                            $counterM++;
                        endforeach;
                    }
                    if($counterM == count($request->getPost()->periodM)){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($request->getPost()->semaine){  ////////////// Semaine
                        $counterS = 0;
                        foreach($request->getPost()->semaine as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            $counterS++;
                        endforeach;

                        if($counterS == count($request->getPost()->semaine)) {
                            $whereS2 .= " )";
                            $whereOther2 .= " )";
                        }
                    }
                }
            }


            $whereS .= $whereS2;        
            $whereOther .= $whereOther2;  


            ///////////////////// PERIODE TEXT ///////////////////////
            $period = "";
            $periodM = "";
            $periodY = "";
            $periodJ = "";
            $monthText = $this->echoMonthText();
            $dayText = $this->echoShortDayText();

            if($request->getPost()->periodM){
                $i = 1;
                foreach($request->getPost()->periodM as $mois):
                    if($i < count($request->getPost()->periodM))
                        $periodM .= $monthText[$mois].", ";
                    else
                        $periodM .= $monthText[$mois];
                    $i++;
                endforeach;
            }
            if($request->getPost()->periodY){
                $i = 1;
                $periodY .= ' ';
                foreach($request->getPost()->periodY as $annee):                    
                    if($i < count($request->getPost()->periodY))
                        $periodY .= $annee.", ";
                    else
                        $periodY .= $annee;
                    $i++;
                endforeach;
            }
            if($request->getPost()->semaine){
                $i = 1;
                $periodJ .= ' - ';
                foreach($request->getPost()->semaine as $jour):                    
                    if($i < count($request->getPost()->semaine))
                        $periodJ .= $dayText[$jour]." ";
                    else
                        $periodJ .= $dayText[$jour];
                    $i++;
                endforeach;
            }

            $period = $periodM.$periodY.$periodJ;
            $period = ($period != "")?$period:"Toute la période";

            $query['where'] = $whereS;
            $query['whereOther'] = $whereOther;
            $query['period'] = $period;
        }
        $query['withMarche'] = $withMarche;

        return $query;        
    }
    
    function getRefreshQuery($storeIds) {
        $query = array();
        
        if($storeIds != "") {
            $storeWhere = "idStore IN (".$storeIds.")";
            $dataCritere = new Container('dataCritere');

            $whereS = $storeWhere;
            $whereOther = $storeWhere;
            $whereS2 = "";
            $whereOther2 = "";


            if($dataCritere->offsetGet('periodY')){
                $counterA = 0;
                if($dataCritere->offsetGet('periodM')){
                    if($dataCritere->offsetGet('semaine')){ ////////////// Année + Mois + Semaine
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('periodM') as $month):
                                foreach($dataCritere->offsetGet('semaine') as $day):
                                    if($whereS2 == "") {
                                        $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }

                                    if($whereOther2 == "") {
                                        $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }
                                endforeach;
                            endforeach;
                            $counterA++;
                        endforeach;
                    } else { ////////////// Année + Mois
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('periodM') as $month):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    }

                    if($counterA == count($dataCritere->offsetGet('periodY'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($dataCritere->offsetGet('semaine')){ ////////////// Année + Semaine 
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('semaine') as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    } else { ////////////// Année
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                            } else {
                                $whereS2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                            } else {
                                $whereOther2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                            }
                            $counterA++;
                        endforeach;
                    }
                    if($counterA == count($dataCritere->offsetGet('periodY'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } 
            } else {
                if($dataCritere->offsetGet('periodM')){
                    $counterM = 0;
                    if($dataCritere->offsetGet('semaine')){ ////////////// Mois + Semaine
                        foreach($dataCritere->offsetGet('periodM') as $month):
                            foreach($dataCritere->offsetGet('semaine') as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterM++;
                        endforeach;
                    } else { ////////////// Mois
                        foreach($dataCritere->offsetGet('periodM') as $month):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                            } else {
                                $whereS2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                            } else {
                                $whereOther2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                            }

                            $counterM++;
                        endforeach;
                    }
                    if($counterM == count($dataCritere->offsetGet('periodM'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($dataCritere->offsetGet('semaine')){  ////////////// Semaine
                        $counterS = 0;
                        foreach($dataCritere->offsetGet('semaine') as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            $counterS++;
                        endforeach;

                        if($counterS == count($dataCritere->offsetGet('semaine'))) {
                            $whereS2 .= " )";
                            $whereOther2 .= " )";
                        }
                    }
                }
            }


            $whereS .= $whereS2;        
            $whereOther .= $whereOther2;

            $query['where'] = $whereS;
            $query['whereOther'] = $whereOther;
        }
        
        return $query;        
    }
    
    function getRefreshMarcheQuery($marcheIds, $prefix="") {
        $query = array();
        
        if($marcheIds != "") {
            $marcheWhere = "idMarche IN (".$marcheIds.")";
            $dataCritere = new Container('dataCritere');

            $whereS = $marcheWhere;
            $whereOther = $marcheWhere;
            $whereS2 = "";
            $whereOther2 = "";


            if($dataCritere->offsetGet('periodY')){
                $counterA = 0;
                if($dataCritere->offsetGet('periodM')){
                    if($dataCritere->offsetGet('semaine')){ ////////////// Année + Mois + Semaine
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('periodM') as $month):
                                foreach($dataCritere->offsetGet('semaine') as $day):
                                    if($whereS2 == "") {
                                        $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                        $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month;
                                        $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }

                                    if($whereOther2 == "") {
                                        $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    } else {
                                        $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                        $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month;
                                        $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                    }
                                endforeach;
                            endforeach;
                            $counterA++;
                        endforeach;
                    } else { ////////////// Année + Mois
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('periodM') as $month):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    }

                    if($counterA == count($dataCritere->offsetGet('periodY'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($dataCritere->offsetGet('semaine')){ ////////////// Année + Semaine 
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            foreach($dataCritere->offsetGet('semaine') as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterA++;
                        endforeach;
                    } else { ////////////// Année
                        foreach($dataCritere->offsetGet('periodY') as $year):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                            } else {
                                $whereS2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                            } else {
                                $whereOther2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                            }
                            $counterA++;
                        endforeach;
                    }
                    if($counterA == count($dataCritere->offsetGet('periodY'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } 
            } else {
                if($dataCritere->offsetGet('periodM')){
                    $counterM = 0;
                    if($dataCritere->offsetGet('semaine')){ ////////////// Mois + Semaine
                        foreach($dataCritere->offsetGet('periodM') as $month):
                            foreach($dataCritere->offsetGet('semaine') as $day):
                                if($whereS2 == "") {
                                    $whereS2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereS2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month;
                                    $whereS2 .= " AND DAYOFWEEK(orderDate) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }

                                if($whereOther2 == "") {
                                    $whereOther2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                } else {
                                    $whereOther2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month;
                                    $whereOther2 .= " AND DAYOFWEEK(date) = ".($day+1).")"; // (DAYOFWEEK, Dimanche=1 et non 0)
                                }
                            endforeach;

                            $counterM++;
                        endforeach;
                    } else { ////////////// Mois
                        foreach($dataCritere->offsetGet('periodM') as $month):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                            } else {
                                $whereS2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                            } else {
                                $whereOther2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                            }

                            $counterM++;
                        endforeach;
                    }
                    if($counterM == count($dataCritere->offsetGet('periodM'))){
                        $whereS2 .= " )";
                        $whereOther2 .= " )";
                    }
                } else {
                    if($dataCritere->offsetGet('semaine')){  ////////////// Semaine
                        $counterS = 0;
                        foreach($dataCritere->offsetGet('semaine') as $day):
                            if($whereS2 == "") {
                                $whereS2 .= " AND ( DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereS2 .= " OR DAYOFWEEK(orderDate) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            if($whereOther2 == "") {
                                $whereOther2 .= " AND ( DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            } else {
                                $whereOther2 .= " OR DAYOFWEEK(date) = ".($day+1); // (DAYOFWEEK, Dimanche=1 et non 0)
                            }

                            $counterS++;
                        endforeach;

                        if($counterS == count($dataCritere->offsetGet('semaine'))) {
                            $whereS2 .= " )";
                            $whereOther2 .= " )";
                        }
                    }
                }
            }


            $whereS .= $whereS2;        
            $whereOther .= $whereOther2;

            $query['where'] = $whereS;
            $query['whereOther'] = $whereOther;
        }

        return $query;     
    }
    
    public function generatePlanningDays($request) {
        $days = array();
        $longDayText = $this->echoLongDayText();
        if(isset($request->getPost()->semaine)) {
            foreach($request->getPost()->semaine as $numDay) {
                array_push($days, $longDayText[$numDay]);
            }
        } else {
            $days = array(
                'lundi', 'mardi', 'mercredi', 'jeudi', 'vendredi', 'samedi', 'dimanche',
            );
        }
        
        return $days;
    }
    
    public function generatePlanningMonths($request) {
        $months = array();
        if(isset($request->getPost()->periodY)) {
            if(isset($request->getPost()->periodM)) {
                $month[] = $request->getPost()->periodY.'-'.($request->getPost()->periodM < 10)?'0'.$request->getPost()->periodM:$request->getPost()->periodM;
            } else {
                $month[] = $request->getPost()->periodY.'-'.date('m');
            }
        } else {
            if(isset($request->getPost()->periodM)) {
                $months[] = date('Y').'-'.($request->getPost()->periodM < 10)?'0'.$request->getPost()->periodM:$request->getPost()->periodM;
            } else {
                $months[] = date('Y').'-'.date('m');
            }
        }
        
        return $months;
    }
    
    public function echoShortDayText() {
        $dayText[0] = 'dim';
        $dayText[1] = 'lun';
        $dayText[2] = 'mar';
        $dayText[3] = 'mer';
        $dayText[4] = 'jeu';
        $dayText[5] = 'ven';
        $dayText[6] = 'sam';        
        
        return $dayText;
    }
    
    public function echoLongDayText() {
        $dayText[0] = 'dimanche';
        $dayText[1] = 'lundi';
        $dayText[2] = 'mardi';
        $dayText[3] = 'mercredi';
        $dayText[4] = 'jeudi';
        $dayText[5] = 'vendredi';
        $dayText[6] = 'samedi';        
        
        return $dayText;
    }
    
    public function echoMonthText() {

        $monthText[1] = 'Janvier';
        $monthText[2] = 'Fevrier';
        $monthText[3] = 'Mars';
        $monthText[4] = 'Avril';
        $monthText[5] = 'Mai';
        $monthText[6] = 'Juin';
        $monthText[7] = 'Juillet';
        $monthText[8] = 'Août';
        $monthText[9] = 'Septembre';
        $monthText[10] = 'Octobre';
        $monthText[11] = 'Novembre';
        $monthText[12] = 'Décembre';
        
        return $monthText;
    }
    
    function WeekNumFromDate($date) {
        list($day, $month, $year) = explode('-', $date);
        $weekNum = (date('W', mktime(0, 0, 0, $month, $day, $year)) * 1);
        return $weekNum;
      }
    
    public function weeksPerMonth($m,$y){
	$day = mktime(1, 1, 1, $m, 1, $y);
	$nday = date('t', $day);
	$fday = date("N",$day);
	$xday = $nday + $fday;
	$n =  $xday % 7 != 0 ? floor($xday/7) +1 : floor($xday/7);
	return $n;
    }
    
    public function getNumberOfWeeks($year, $month) {
	$time = mktime(0, 0, 0, $month, 1, $year);
	$dayInMonth = date('t', $time);	
	if($dayInMonth == 28)
            return date('N', $time) == 1 ? 4 : 5; 
	if($dayInMonth == 29) 
            return 5;
	if($dayInMonth == 30)
            return date('N', $time) == 7 ? 6 : 5;
        
	return date('N', $time) < 6 ? 5 : 6;
    }
}


